﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Globalization;

using System.Text;
using System.IO;
using System.Drawing;
//using iTextSharp.text;
//using iTextSharp.text.pdf;
//using iTextSharp.text.html;
//using iTextSharp.text.html.simpleparser;
//using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;


public partial class Export : System.Web.UI.Page
{
    SqlConnection myConn;
        DataTable myTable; 
    dbConn db = new dbConn(); string mysql;
    protected void Page_Load(object sender, EventArgs e)
    {
        bindGV();
       
       

    }

    private void bindGV()
    {
          mysql = "SELECT LastName,FirstName,ContactPhone, Email,[Address1] ,[State],[City] ,[Zip], ";
          mysql += " Transfer = CASE   [TransferYN]  WHEN 0 THEN 'NO' ";
          mysql += " WHEN 1 THEN 'YES' END, ";
          mysql += " NursingCAS = CASE   [NursingCASYN]  WHEN 0 THEN 'NO' ";
          mysql += " WHEN 1 THEN 'YES' END ";
         mysql +=  " FROM nursing_supplemental_application Order by LastName";
        dg.DataSource = db.CallViews(mysql);
        dg.DataBind();
    }


   
    public override void VerifyRenderingInServerForm(Control control)
    {
        base.VerifyRenderingInServerForm(control);
    }

    protected void Button2_Click(object sender, EventArgs e)
    {   
         myTable = new DataTable(); 
        //////////////////////////////////////////////////////
        //     ORDER BY LAST NAME                           //
        //////////////////////////////////////////////////////
         mysql = "SELECT LastName, MiddleName, FirstName ,   Email, ";
         mysql += " Transfer = CASE   [TransferYN]  WHEN 0 THEN 'NO' ";
         mysql += " WHEN 1 THEN 'YES' END, ";
         mysql += " NursingCAS = CASE   [NursingCASYN]  WHEN 0 THEN 'NO' ";
         mysql += " WHEN 1 THEN 'YES' END, ";
        mysql += "ContactPhone ,Address = [Address1]  + ' ' +[Address2]";

       mysql += ",[State] ,[City]  ,[Zip] ,[Interview1]  ,[Interview2],[Interview3], TimeStamp As SubmittedDate";
       mysql += " FROM nursing_supplemental_application Order By LastName";



        using (myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["prod"].ConnectionString))
        {

            myConn.Open();
            SqlDataAdapter da = new SqlDataAdapter(mysql, myConn);
            da.Fill(myTable);
            //Export1(myTable, "NursingReport.xls");

            string attachment = "attachment; filename=NursingReport.xls";

            Response.ClearContent();

            Response.AddHeader("content-disposition", attachment);

            Response.ContentType = "application/vnd.ms-excel";

            string tab = "";

            foreach (DataColumn dc in myTable.Columns)
            {

                Response.Write(tab + dc.ColumnName);

                tab = "\t";

            }

            Response.Write("\n");



            int i;

            foreach (DataRow dr in myTable.Rows)
            {

                tab = "";

                for (i = 0; i < myTable.Columns.Count; i++)
                {

                    Response.Write(tab + dr[i].ToString());

                    tab = "\t";

                }

                Response.Write("\n");

            }

            Response.End();






        }
        
     

    }

    protected void dg_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        dg.PageIndex = e.NewPageIndex;
        bindGV();
    }





    public static void Export1(System.Data.DataTable table, string filePath)
    {

        HttpContext context = HttpContext.Current;
        context.Response.Clear();
        foreach (DataColumn column in table.Columns)
        {

            context.Response.Write(column.ColumnName + ";");

        }
        context.Response.Write(Environment.NewLine);

        foreach (DataRow row in table.Rows)

        {

            for (int i = 0; i < table.Columns.Count; i++)

            {

                context.Response.Write(row[i].ToString().Replace(";", string.Empty) + ";");

                }

            context.Response.Write(Environment.NewLine);

            }

        context.Response.ContentType = "application/excel";// "text/csv"; 
            
            context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + filePath ); //+ ".csv");

            context.Response.End();

        }


    //Not working
    // //Response.ClearContent();
    // Response.Clear();
    // Response.AddHeader("content-disposition", "attachment; filename=NursingReport.xls");
    //// Response.ContentType = "application/excel";
    // Response.ContentType = "application/vnd.ms-excel";

    // Page.EnableViewState = false;
    // System.IO.StringWriter sw = new System.IO.StringWriter();
    // HtmlTextWriter htw = new HtmlTextWriter(sw);
    // dg.RenderControl(htw);
    // Response.Write(sw.ToString());
    // Response.End();
    //HtmlForm form = new HtmlForm();

    //string attachment = "attachment; filename=Employee.xls";

    //Response.ClearContent();

    //Response.AddHeader("content-disposition", attachment);

    //Response.ContentType = "application/ms-excel";

    //StringWriter stw = new StringWriter();

    //HtmlTextWriter htextw = new HtmlTextWriter(stw);

    //form.Controls.Add(dg);

    //this.Controls.Add(form);

    //form.RenderControl(htextw);

    //Response.Write(stw.ToString());

    //Response.End();

    
}
